package com.mbc.common.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import com.mbc.common.CommonConstants;
import com.mbc.common.entities.User;
import com.mbc.common.data.UserDataList;
import com.mbc.common.param.CriteriaParams;

public class UserDAOImpls extends DAO<User> implements UserDAO {

	public UserDAOImpls(Connection conn)
	{
		this.conn = conn;
		this.table_name = "[User]";
	}

	@Override
	public void create(User entity) throws SQLException {
		query = "insert into " + table_name + " ( UserID, UserName, LoginId, Password, Status, Module, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
				pstmt.setLong(i++, entity.getUserId());
		pstmt.setString(i++, entity.getUserName());
		pstmt.setString(i++, entity.getLoginId());
		pstmt.setString(i++, entity.getPassword());
		pstmt.setByte(i++, entity.getStatus());
		pstmt.setString(i++, entity.getModule());
		pstmt.setDate(i++, new java.sql.Date(entity.getCreatedDate().getTime()));
		pstmt.setLong(i++, entity.getCreatedBy());
		pstmt.setDate(i++, new java.sql.Date(entity.getUpdatedDate().getTime()));
		pstmt.setLong(i++, entity.getUpdatedBy());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(User entity) throws SQLException {
		query = "update " + table_name + " set  UserName = ?, LoginId = ?, Password = ?, Status = ?, Module = ?, UpdatedDate = ?, UpdatedBy = ?" + 
				" where userid = " + entity.getUserId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
				pstmt.setString(i++, entity.getUserName());
		pstmt.setString(i++, entity.getLoginId());
		pstmt.setString(i++, entity.getPassword());
		pstmt.setByte(i++, entity.getStatus());
		pstmt.setString(i++, entity.getModule());
		pstmt.setDate(i++, new java.sql.Date(entity.getUpdatedDate().getTime()));
		pstmt.setLong(i++, entity.getUpdatedBy());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = "update " + table_name + " set Status = " + CommonConstants.RecordStatus.delete +
				" where userid = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public User find(long key) throws SQLException {
		User l_Users = null;
		query = getSelectQuery() + " where userid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_Users = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Users;
	}

	@Override
	public ArrayList<User> findAll() throws SQLException {
		ArrayList<User> l_UsersList = new ArrayList<User>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_UsersList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_UsersList;
	}

	@Override
	protected User readRecord(ResultSet rs) throws SQLException {
		User l_entity = new User();
		l_entity.setUserId(rs.getLong("UserID"));
		l_entity.setUserName(rs.getString("UserName"));
		l_entity.setLoginId(rs.getString("LoginId"));
		l_entity.setPassword(rs.getString("Password"));
		l_entity.setStatus(rs.getByte("Status"));
		l_entity.setModule(rs.getString("Module"));
		l_entity.setCreatedDate(rs.getDate("CreatedDate"));
		l_entity.setCreatedBy(rs.getLong("CreatedBy"));
		l_entity.setUpdatedDate(rs.getDate("UpdatedDate"));
		l_entity.setUpdatedBy(rs.getLong("UpdatedBy"));

		return l_entity;
	}

	@Override
	public UserDataList getList(CriteriaParams cri) throws SQLException{
		UserDataList l_DataList = new UserDataList();
		ArrayList<User> l_entityList = new ArrayList<User>();
		String l_cri = "";
		query = "set rowcount " + cri.getSize() +
			" select * from (select row_number() over(order by UserId desc) as [rowno], * from " + table_name +
			" where 1 = 1" + l_cri +
			" and Status <> " + CommonConstants.RecordStatus.delete +
			") " + table_name +
			" where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()) {
			do {	
				l_entityList.add(readRecord(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(UserId) from " + table_name + " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
    }

	@Override
	public User findByLoginId(String loginId) throws SQLException {
		User l_User = null;
		query = getSelectQuery() + " where loginid = '" + loginId + "'";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_User = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_User;
	}

	@Override
	public void resetPassword(long userId,
			String newPassword) throws SQLException {
		query = "update " + table_name + " set password = ? where userid = " + userId;
		pstmt = conn.prepareStatement(query);
		pstmt.setString(1, newPassword);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void updateUserOnlineStatus(long userId, byte status) throws SQLException {
		query = "update " + table_name + " set Status = " + status + " where userid = " + userId;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void updateLatestLoginDateTime(Date latestLoginDateTime, long userId)
			throws SQLException {
		query = "update " + table_name + " set LatestLoginDateTime = ? where UserId = " + userId;
		pstmt = conn.prepareStatement(query);
		pstmt.setTimestamp(1, new java.sql.Timestamp(latestLoginDateTime.getTime()));
		pstmt.executeUpdate();
		pstmt.close();
	}
}
